﻿using FAMailUtils.Data;
using FAMailUtils.Data.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FAMailUtils.Data.DAO
{
    public class GroupDAO
    {
        DbHelper db = new DbHelper();
        public GroupDTO GetByGroupID(int GroupID, int UserID)
        {
            string sql = "select * from tblMB_Groups where GroupID = " + GroupID + " and UserID = " + UserID;
            GroupDTO result = new GroupDTO();
            result = (GroupDTO)db.GetObject(result, sql);
            return result;
        }

        public DataTable GetAccountByGroupID(int GroupID, int UserID)
        {
            string sql = "select a.* from tblMB_Groups g, tblMB_Accounts a, tblMB_AccountsInGroup ag " +
                            "where g.IsDelete = 0 and ag.AccountID = a.AccountID and ag.GroupID = g.GroupID and g.GroupID = @GroupID and g.UserID = @UserID";
            DataTable T = db.GetTable(sql, new object[] { "GroupID", GroupID, "UserID", UserID });
            return T;
        }

        public DataTable GetAll(int UserID)
        {
            string sql = @"select g.*, (select count(*) from tblMB_AccountsInGroup ag inner join tblMB_Accounts a on a.AccountID = ag.AccountID where ag.GroupID = g.GroupID and a.IsDelete=0) NumberOfAccounts 
                        from tblMB_Groups g where isdelete=0 and UserID = @UserID";
            DataTable T = db.GetTable(sql, new object[] { "UserID", UserID });
            return T;
        }

        public void RemoveAccountFromGroup(int GroupID, int AccountID)
        {
            string sql = "delete from tblMB_AccountsInGroup where GroupID = " + GroupID + " and AccountID = " + AccountID;
            db.ExecuteNoneQuery(sql);
        }

        public bool Edit(GroupDTO dto)
        {
            string sql = "update tblMB_Groups set GroupName = @GroupName, Description = @Description, groupmail=@groupmail from tblMB_Groups where groupid= @groupid and userid = @userid";
            object obj = db.ExecuteScalar(sql, new object[] { "GroupID", dto.GroupID, "GroupName", dto.GroupName, "Description", dto.Description, "groupmail", dto.GroupMail, "UserID", dto.UserID });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }
        public int Add(GroupDTO dto)
        {
            string sql = "insert into tblMB_Groups (GroupName, Description, GroupMail, UserID, CreateDate, IsDelete) values (@GroupName, @Description, @GroupMail, @UserID, getdate(), @IsDelete)";
            object obj = db.ExecuteScalar(sql, new object[] { "GroupName", dto.GroupName, "Description", dto.Description, "groupmail", dto.GroupMail, "UserID", dto.UserID, "IsDelete", false });
            int result = 0;
            int.TryParse(obj + "", out result);
            return result;
        }
        public bool Delete(int GroupID, int UserID)
        {
            string sql = "update tblMB_Groups set isDelete = 1, deletedate = getdate() from tblMB_Groups where groupid=@groupid and userid = @userid";
            object obj = db.ExecuteScalar(sql, new object[] { "GroupID", GroupID, "UserID", UserID });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }

        // Kiểm tra trùng
        public string CheckName(string name, int id)
        {
            string sql = "select * from tblMB_Groups where IsDelete = 0 and GroupName = '" + name + "' and GroupID != " + id;
            GroupDTO result = new GroupDTO();
            result = (GroupDTO)db.GetObject(result, sql);
            if (String.IsNullOrWhiteSpace(result.GroupName))
            {
                return "";
            }
            else
            {
                return "Tên nhóm đã tồn tại.";
            }
        }
        // Kiểm tra trùng
        public string CheckGroupMail(string groupmail, int id)
        {
            string sql = "select * from tblMB_Groups where IsDelete = 0 and groupmail = @groupmail and GroupID != " + id;
            GroupDTO result = new GroupDTO();
            result = (GroupDTO)db.GetObject(result, sql, new object[] { "groupmail", groupmail });
            if (String.IsNullOrWhiteSpace(result.GroupName))
            {
                return "";
            }
            else
            {
                return "Email nhóm đã tồn tại.";
            }
        }

        public DataTable GetAccountByDepartmentID(int dID, int groupid, int userid)
        {
            string sql = @"select accountid, accountname, email, accountname +'<'+email+'>' mix
                        from tblMB_Accounts a
                        where (@dID=0 or a.DepartmentID=@dID) and userid = @userid and a.IsDelete <> 1
                        and a.AccountID not in (select AccountID from tblMB_AccountsInGroup g where g.GroupID=@GroupID)";
            return db.GetTable(sql, new object[] { "dID", dID, "groupid", groupid ,"userid", userid});
        }

        public void AddAccountToGroup(int accountid, int groupid)
        {
            string sql = "insert into tblMB_AccountsInGroup(AccountID, GroupID, DateAdd) values(@AccountID, @GroupID, getdate())";
            db.ExecuteNoneQuery(sql, new object[] { "accountid", accountid, "groupid", groupid });
        }
    }
}
